package net.fenghaitao;

import lombok.extern.slf4j.Slf4j;
import net.fenghaitao.domain.BlockNameResolver;
import net.fenghaitao.domain.ExcelSetting;
import net.fenghaitao.enums.AggregateType;
import net.fenghaitao.enums.Constants;
import net.fenghaitao.enums.DataDirection;
import net.fenghaitao.enums.DataSourceType;
import net.fenghaitao.managers.AggregateCellManager;
import net.fenghaitao.managers.BaseCellManager;
import net.fenghaitao.managers.CellManager;
import net.fenghaitao.managers.RowNoCellManager;
import net.fenghaitao.parameters.DirectExportPara;
import net.fenghaitao.parameters.FieldSetting;
import net.fenghaitao.parameters.TemplateExportPara;
import net.fenghaitao.utils.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.util.*;
import java.util.function.Consumer;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

/**
 * @author ：HuangXiong
 * @date ：Created in 2021/1/11 16:06
 * @description：导出excel工具类
 * @modified By：
 * @version: 1.0
 */
@Slf4j
public class ExcelWriter {

    private ExcelWriter (){}

    private static final Pattern WRITE_FORMULA_PATTERN = Pattern.compile("([a-z]+)([0-9]+)", Pattern.CASE_INSENSITIVE);

    /**
     * Generate Excel according to the specified template and parameters
     *
     * @param templatePath       the excel template path
     * @param outputPath         output path
     * @param templateExportPara the parameter for exporting with template
     */
    public static void save(String templatePath, String outputPath, TemplateExportPara templateExportPara) throws Exception {
        save(templatePath, outputPath, Arrays.asList(templateExportPara));
    }

    /**
     * Generate Excel according to the specified template and parameters
     *
     * @param templatePath        the excel template path
     * @param outputPath          output path
     * @param templateExportParas the parameters for exporting with template
     */
    public static void save(String templatePath, String outputPath, List<TemplateExportPara> templateExportParas) throws Exception {
        save(templatePath, outputPath, templateExportParas, null, null);
    }

    /**
     * Generate Excel according to the specified template and parameters
     *
     * @param templatePath        the excel template path
     * @param outputPath          output path
     * @param templateExportParas the parameters for exporting with template
     * @param excelSetting        Excel Setting
     */
    public static void save(String templatePath, String outputPath, List<TemplateExportPara> templateExportParas,
                            ExcelSetting excelSetting) throws Exception {
        Consumer<Workbook> actionBehind = null;
        if (excelSetting != null && excelSetting.getRemovedSheets() != null) {
            actionBehind = workbook -> {
                excelSetting.getRemovedSheets().forEach(sheetName -> {
                    int sheetIndex = workbook.getSheetIndex(sheetName);
                    if (sheetIndex >= 0)
                        workbook.removeSheetAt(sheetIndex);
                });
            };
        }
        save(templatePath, outputPath, templateExportParas, null, actionBehind);
    }
    /**
     * Generate Excel directly
     *
     * @param outputPath       output path
     * @param directExportPara the parameter for exporting directly
     */
    public static void save(String outputPath, DirectExportPara directExportPara) throws Exception {
        List<DirectExportPara> directExportParas = new ArrayList<>();
        directExportParas.add(directExportPara);
        save(outputPath, directExportParas);
    }

    /**
     * Generate Excel according to the specified template and parameters
     *
     * @param templatePath        the excel template path
     * @param outputPath          output path
     * @param templateExportParas the parameters for exporting with template
     * @param actionAhead         action before internal operations
     * @param actionBehind        action after internal operations
     */
    public static void save(String templatePath,
                            String outputPath,
                            List<TemplateExportPara> templateExportParas,
                            Consumer<Workbook> actionAhead,
                            Consumer<Workbook> actionBehind) throws Exception {
        if (!(new File(templatePath)).exists())
            throw new IOException("Cannot find template file: " + templatePath);

        Workbook workbook = new XSSFWorkbook(templatePath);

        //action before internal operations
        if (actionAhead != null)
            actionAhead.accept(workbook);

        List<BlockNameResolver<TemplateExportPara>> blockNameResolvers = CellUtil.resolveCellNames(workbook, templateExportParas);

        //cache the properties of data source to be used
        Map<String, Map<String, Field>> dataSourceNameFields = new HashMap<>(templateExportParas.size());
        for (TemplateExportPara templateExportPara : templateExportParas) {
            if (templateExportPara.getDataSource() != null && templateExportPara.getRecordCount() > 0)
                dataSourceNameFields.put(
                        templateExportPara.getDataSourceName().toLowerCase(),
                        getFieldMap(templateExportPara.getObjectType())
                );
        }

        //insert data into template
        for (BlockNameResolver<TemplateExportPara> blockNameResolver : blockNameResolvers) {
            TemplateExportPara templateExportPara = blockNameResolver.getPara();
            if (templateExportPara == null) continue;

            Object dataSource = templateExportPara.getDataSource();
            if (dataSource == null) continue;

            Sheet sheet = blockNameResolver.getSheet();
            int recordCount = templateExportPara.getRecordCount();
            //if data source is List
            if (DataSourceType.List == templateExportPara.getDataSourceType()) {
                if (templateExportPara.isInserted() && recordCount > 1) {
                    int startRow = blockNameResolver.getFieldNameCells().entrySet().iterator().next().getValue().getRowIndex() + 1;
                    //Move the other rows to free up enough space to populate the current data source
                    sheet.shiftRows(startRow, sheet.getLastRowNum(), recordCount - 1, true, false);
                    //After the row is moved, the original cell position changes and the cell location saved in the cell
                    //name manager needs to be refreshed
                    reLocate(workbook, blockNameResolvers, blockNameResolver.getSheetName());
                }

                int i = 0;
                for (Object record : (List) dataSource) {
                    writeRecordByCellName(sheet, blockNameResolver, record, dataSourceNameFields, i);
                    ++i;
                }

                writeAggregate(sheet, blockNameResolver, recordCount);
                writeFormula(sheet, blockNameResolver, recordCount);

                RowNoCellManager rowNoCellMgr = blockNameResolver.getRowNoCellManager();
                if (templateExportPara.getDataDirection() == DataDirection.Down && rowNoCellMgr != null)
                    writeRowNo(sheet, rowNoCellMgr, recordCount);

                //if data direction is right, the column width needs to be adaptive
                if (templateExportPara.getDataDirection() == DataDirection.Right) {
                    int startColIndex = blockNameResolver.getFieldNameCells().entrySet().iterator().next().getValue().getColIndex();
                    for (int j = 0; j < recordCount; ++j)
                        sheet.autoSizeColumn(startColIndex + j);
                }
                //recalculate formula
                XSSFFormulaEvaluator.evaluateAllFormulaCells(workbook);
            }
            //if data source is a basic object
            else {
                writeRecordByCellName(sheet, blockNameResolver, dataSource, dataSourceNameFields, 0);
            }
        }

        //action after internal operations
        if (actionBehind != null)
            actionBehind.accept(workbook);

        try (FileOutputStream stream = new FileOutputStream(outputPath)) {
            workbook.write(stream);
        }
    }


    /**
     * Generate Excel directly
     *
     * @param outputPath        output path
     * @param directExportParas the parameters for exporting directly
     */
    public static void save(String outputPath, List<DirectExportPara> directExportParas) throws Exception {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet;
        //create style of title
        Font font = workbook.createFont();
        font.setColor(IndexedColors.WHITE.index);
        CellStyle headStyle = workbook.createCellStyle();
        headStyle.setFillForegroundColor(IndexedColors.ORCHID.getIndex());
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setFont(font);

        for (DirectExportPara directExportPara : directExportParas) {
            if (directExportPara.getDataSource() == null)
                continue;

            if (directExportPara.getSheetName() == null || directExportPara.getSheetName().isEmpty())
                sheet = workbook.createSheet();
            else
                sheet = workbook.createSheet(directExportPara.getSheetName());

            DataSourceType dataSourceType = directExportPara.getDataSourceType();
            Map<String, Field> fieldNameFields = ReflectUtils.getFieldMap(directExportPara.getObjectType());

            List<FieldSetting> fieldSettings = directExportPara.getFieldSettings();
            if (fieldSettings == null || fieldSettings.size() == 0) {
                fieldSettings = fieldNameFields.values()
                        .stream()
                        .map(m -> new FieldSetting(m.getName(), m.getName()))
                        .collect(Collectors.toList());
            }
            int rowIndex = 0;
            int colIndex = 0;
            //write title
            for (FieldSetting fieldSetting : fieldSettings) {
                SheetUtil.setValue(sheet, rowIndex, colIndex, fieldSetting.getDisplayName())
                        .setCellStyle(headStyle);
                ++colIndex;
            }
            //write data
            ++rowIndex;
            if (dataSourceType == DataSourceType.List) {
                for (Object record : (List) directExportPara.getDataSource()) {
                    writeRecordByFieldSetting(sheet, fieldSettings, record, fieldNameFields, rowIndex);
                    ++rowIndex;
                }
            } else {
                writeRecordByFieldSetting(sheet, fieldSettings, directExportPara.getDataSource(),
                        fieldNameFields, rowIndex);
            }
            //auto size the columns
            for (int i = 0; i < fieldSettings.size(); ++i)
                sheet.autoSizeColumn(i);
        }

        try (FileOutputStream stream = new FileOutputStream(outputPath)) {
            workbook.write(stream);
        }
    }

    /**
     * Write a record to sheet according to the fieldSettings
     */
    private static void writeRecordByFieldSetting(Sheet sheet,
                                                  List<FieldSetting> fieldSettings,
                                                  Object dataSourceRecord,
                                                  Map<String, Field> fieldNameFields, int rowIndex) throws IllegalAccessException {
        int colIndex = 0;
        for (FieldSetting fieldSetting : fieldSettings) {
            String fieldName = fieldSetting.getFieldName().toLowerCase();
            if (fieldNameFields.containsKey(fieldName)) {
                Field field = fieldNameFields.get(fieldName);
                field.setAccessible(true);
                Object value = field.get(dataSourceRecord);
                SheetUtil.setValue(sheet, rowIndex, colIndex, value);
            }
            ++colIndex;
        }
    }


    /**
     * ReLocate the cell. After the row is moved, the original cell position changes and the cell location saved in the
     * cell name manager needs to be refreshed
     */
    private static void reLocate(Workbook workbook, List<BlockNameResolver<TemplateExportPara>> blockNameResolvers, String currSheetName) {
        for (BlockNameResolver<TemplateExportPara> blockNameResolver : blockNameResolvers) {
            if (!blockNameResolver.getSheetName().equals(currSheetName))
                continue;
            Pattern pattern = Constants.getNamePattern(currSheetName);
            /**
             * (重新定位) 普通单元格、汇总单元格、公式单元格、行号单元格
             */
            reLocate(workbook, pattern, blockNameResolver.getFieldNameCells().values());
            reLocate(workbook, pattern, blockNameResolver.getFieldNameAggregateCells().values());
            reLocate(workbook, pattern, blockNameResolver.getFormulaCellManagers());
            reLocate(workbook, pattern, Arrays.asList(blockNameResolver.getRowNoCellManager()));

            blockNameResolver.getFormulaCellManagers().forEach(formulaCellMgr -> {
                Cell cell = SheetUtil.getOrCreateCell(blockNameResolver.getSheet(), formulaCellMgr.getRowIndex(),
                        formulaCellMgr.getColIndex());
                formulaCellMgr.setFormula(cell.getCellFormula());
            });
        }
    }

    private static void reLocate(Workbook workbook, Pattern pattern, Collection<? extends BaseCellManager> baseCellManagers) {

        for (BaseCellManager baseCellManager : baseCellManagers) {
            if(Objects.isNull(baseCellManager)){
                continue;
            }
            String refer = workbook.getName(baseCellManager.getCellName()).getRefersToFormula();
            Matcher matcher = pattern.matcher(refer);
            if (matcher.find()) {
                baseCellManager.setRowIndex(Integer.parseInt(matcher.group(2)) - 1);
                baseCellManager.setColIndex(colNameToIndex(matcher.group(1)));
            }
        }
    }


    /**
     * Converts the column name to the index location，eg.AB -> 27
     */
    private static int colNameToIndex(String chars) {
        if (chars.isEmpty())
            return -1;

        int index = 0;
        int place = chars.length();
        int tmp = 0;

        for (int i = 0; i < chars.length(); ++i) {
            tmp = Convert.charToNum(chars.charAt(i));
            if (place > 1)
                index += 26 * (place - 1) * (tmp + 1);
            else
                index += tmp;

            --place;
        }
        return index;
    }

    /**
     * Write a record to sheet according to the cell name
     */
    private static void writeRecordByCellName(Sheet sheet,
                                              BlockNameResolver<TemplateExportPara> templateExcelManager,
                                              Object dataSourceRecord,
                                              Map<String, Map<String, Field>> dataSourceNameFields,
                                              int step) throws IllegalAccessException {
        for (Map.Entry<String, CellManager> entry : templateExcelManager.getFieldNameCells().entrySet()) {
            String fieldName = entry.getKey().toLowerCase();
            CellManager cellManager = entry.getValue();
            String dataSourceName = templateExcelManager.getDataSourceName();
            if (dataSourceNameFields.containsKey(dataSourceName) && dataSourceNameFields.get(dataSourceName).containsKey(fieldName)) {
                Field field = dataSourceNameFields.get(dataSourceName).get(fieldName);
                field.setAccessible(true);
                Object cellValue = field.get(dataSourceRecord);
                int rowIndex = cellManager.getRowIndex();
                int colIndex = cellManager.getColIndex();

                if (templateExcelManager.getPara().getDataDirection() == DataDirection.Down)
                    rowIndex += step;
                else
                    colIndex += step;

                Cell cell = SheetUtil.setValue(sheet, rowIndex, colIndex, cellValue);
                if (templateExcelManager.getPara().isCopyCellStyle())
                    cell.setCellStyle(cellManager.getCellStyle());
            }
        }
    }

    /**
     * Assign value to the aggregate cells
     */
    private static void writeAggregate(Sheet sheet, BlockNameResolver<TemplateExportPara> templateExcelManager, int recordCount) {
        for (String fieldName : templateExcelManager.getFieldNameAggregateCells().keySet()) {
            AggregateCellManager aggregateCellManager = templateExcelManager.getFieldNameAggregateCells().get(fieldName);
            Cell aggregateCell = SheetUtil.getOrCreateCell(sheet, aggregateCellManager.getRowIndex(), aggregateCellManager.getColIndex());
            if (templateExcelManager.getFieldNameCells().containsKey(fieldName)) {
                CellManager cellMgr = templateExcelManager.getFieldNameCells().get(fieldName);
                int rowIndex = cellMgr.getRowIndex();
                int colIndex = cellMgr.getColIndex();
                String colName = cellMgr.getColName();

                switch (templateExcelManager.getPara().getDataDirection()) {
                    case Down:
                            aggregateCell.setCellFormula(String.format(
                                    aggregateCellManager.getAggregateType().getAggregateFormulaByDown(), colName,
                                    rowIndex + 1, rowIndex + recordCount));
                        break;
//                    case Right:
//                        break;
                    default:
                        aggregateCell.setCellFormula(String.format(
                                aggregateCellManager.getAggregateType().getAggregateFormula() , colName,
                                rowIndex + 1, indexToColName(colIndex + recordCount - 1)
                        ));
                        break;
                }
            }
        }
    }

    /**
     * Write formula
     */
    private static void writeFormula(Sheet sheet, BlockNameResolver<TemplateExportPara> templateExcelManager, int recordCount) {
        templateExcelManager.getFormulaCellManagers().forEach(formulaCellManager -> {
            for (int i = 1; i < recordCount; ++i) {

                Matcher matcher = WRITE_FORMULA_PATTERN.matcher(formulaCellManager.getFormula());
                StringBuffer sb = new StringBuffer();
                while (matcher.find()) {
                    String colName = matcher.group(1);
                    String rowIndex = matcher.group(2);
                    String replacement;
                    if (templateExcelManager.getPara().getDataDirection() == DataDirection.Down)
                        replacement = colName + (Integer.parseInt(rowIndex) + i);
                    else
                        replacement = getColName(colName, i) + rowIndex;

                    matcher.appendReplacement(sb, replacement);
                }
                matcher.appendTail(sb);
                String formula = sb.toString();

                int newRowIndex = formulaCellManager.getRowIndex();
                int newColIndex = formulaCellManager.getColIndex();
                if (templateExcelManager.getPara().getDataDirection() == DataDirection.Down)
                    newRowIndex += i;
                else
                    newColIndex += i;

                Cell cell = SheetUtil.getOrCreateCell(sheet, newRowIndex, newColIndex);
                cell.setCellFormula(formula);
                cell.setCellStyle(formulaCellManager.getCellStyle());
            }
        });
    }

    /**
     * Write row no.
     */
    private static void writeRowNo(Sheet sheet, RowNoCellManager rowNoCellMgr, int recordCount) {
        for (int i = 0; i < recordCount; ++i) {
            SheetUtil.setValue(sheet, rowNoCellMgr.getRowIndex() + i, rowNoCellMgr.getColIndex(), i + 1)
                    .setCellStyle(rowNoCellMgr.getCellStyle());
        }
    }

    /**
     * Get the Excel column name based on the given column name and step size
     */
    private static String getColName(String colName, int step) throws IllegalArgumentException {
        return indexToColName(colNameToIndex(colName) + step);
    }
    /**
     * Converts the column index (starting at 0) to the Excel column name
     */
    private static String indexToColName(int colIndex) throws IllegalArgumentException {
        List<Character> chars = new ArrayList<>();
        if (colIndex > 25) {
            int left = colIndex % 26;
            chars.add(Convert.numToChar(left));
            colIndex = colIndex / 26;
        } else {
            return String.valueOf(Convert.numToChar(colIndex));
        }
        while (colIndex > 26) {
            int left = colIndex % 26;
            chars.add(Convert.numToChar(left - 1));
            colIndex = colIndex / 26;
        }
        chars.add(Convert.numToChar(colIndex - 1));
        Collections.reverse(chars);
        return Arrays.toString(chars.toArray());
    }

    public static Map<String,Field> getFieldMap(Class clazz){
        Field[] declaredFields = clazz.getDeclaredFields();
        HashMap<String, Field> fieldHashMap = new HashMap<>(declaredFields.length);
        for (Field field :declaredFields)
            fieldHashMap.put(field.getName().toLowerCase(), field);

        return fieldHashMap;
    }
}
